create table commodity_category( --商品总类别
	brand_id	int	primary key, --品牌id
	brand_name	varchar(255),	 --品牌名称
	category	varchar(255)	 --商品类别
);

create table appliances(
	brand_id		int references commodity_category(brand_id),
	category_id 	int primary key,	--种类id
	category_name	varchar(255),		--种类名称
	brand_name		varchar(255),
	prices			int,
	sales_volume	int,				--销量
	stock			int					--库存
);

create table air_cond(					--空调参数表
	category_id				int	primary key,
	category_name			varchar(255),
	energy_effiency_ratio	real,		--能效比
	heating_capacity		int,		--制热量
	heating_power			int,		--制热功率
	cooling					int,		--制冷量
	cooling_power			int,		--制冷功率
	foreign key (category_id) references appliances(category_id)
);

create table washing_machine(			--洗衣机参数表
	category_id				int	primary key,
	category_name			varchar(255),
	water_consumption		int,		--耗水量
	elec_consumption		int,		--耗电量
	volume					int,		--容量
	weight					int,		--重量
	foreign key (category_id) references appliances(category_id)
);

create table television(
	category_id				int	primary key,
	category_name			varchar(255),
	fps						int,		--屏幕刷新率
	screen_size				real,		--屏幕尺寸
	net						boolean,	--是否联网
	power_supply			int,		--电源功率
	foreign key (category_id) references appliances(category_id)
);

create table heater(					--热水器参数表
	category_id				int	primary key,
	category_name			varchar(255),
	hotwater_production		int,		--热水产率
	heatingmode				varchar(255),	--加热方式
	heating_power			int,		--功率
	foreign key (category_id) references appliances(category_id)
);

create table digital(					--数码用品
	brand_id		int references commodity_category(brand_id),
	category_id 	int primary key,	--种类id
	category_name	varchar(255),		--种类名称
	brand_name		varchar(255),
	prices			int,
	sales_volume	int,				--销量
	stock			int				--库存
);

create table computer(					--电脑参数
	category_id				int	primary key,
	category_name			varchar(255),
	memory					int,		--内存
	hard_disk				int,		--硬盘
	graphics_card			varchar(255),	--显卡类型 集成 or 独立显卡
	graphics_card_name		varchar(255),	--显卡名称
	cpu						varchar(255),	--cpu型号
	cores					int,			--处理器核心数
	power_supply			int,			--电源功率
	foreign key (category_id) references appliances(category_id)
);

create table phone(
	category_id				int	primary key,
	category_name			varchar(255),
	cpu						varchar(255),
	front_camera			boolean,		--是否有前置摄像头
	camera_pixel			int,			--后置摄像头像素
	ram						int,
	memory					int,
	foreign key (category_id) references appliances(category_id)
);

create table camera(
	category_id				int	primary key,
	category_name			varchar(255),
	pixel					int,			--摄像头像素
	weight					int,			--产品净重
	foreign key (category_id) references appliances(category_id)
);

create table shoes_clothes(
	brand_id		int references commodity_category(brand_id),
	category_id 	int primary key,	--种类id
	item_name		varchar(255),		--种类名称，相当于digital.category_name
	category_name	varchar(255),		--货品名称，相当于camera.categoy_name
	brand_name		varchar(255),
	prices			int,
	sales_volume	int,				--销量
	stock			int					--库存	
);

create table user_imformation(
	user_id			int		primary key,
	pwd				varchar(255)
);

create table administrators(		--管理员信息
	admini_id	int 	primary key,
	pwd			varchar(255)
);

create table order_list(		--订单表
	user_id			int,
	category_name	varchar(255),
	category_id		int
);

create table all_goods(
	category_id		int,
	item_name		varchar(255),	--种类名称
	category_name	varchar(255),	--（具体的）货品名称
	primary key(category_id, item_name)
);

select * from user_imformation

copy all_goods
from 'E:\vscode-project\database\taobao\data\total.txt'
with(
	DELIMITER ','
);

select * from order_list


update appliances
set sales_volume = sales_volume + 1, stock = stock - 1
where category_id = 1 and category_name = '空调'

delete from 


select air_cond.category_name, prices, sales_volume, stock, energy_effiency_ratio, appliances.category_id, appliances.category_name
from appliances, air_cond
where appliances.category_id = air_cond.category_id


--触发器，库存不能小于0
create or replace function insert_departments() returns trigger as $$
	begin 
	update appliances 
	set stock = new.stock + 1, sales_volume = new.sales_volume - 1
	where category_id = new.category_id;
	return new;
	end;
$$ language plpgsql;

create trigger departmentsTrigger
after update of stock on appliances
for each row 
when (new.stock < 0)
execute procedure insert_departments();



create or replace function digital_update() returns trigger as $$
	begin 
	update digital 
	set stock = new.stock + 1, sales_volume = new.sales_volume - 1
	where category_id = new.category_id;
	return new;
	end;
$$ language plpgsql;

create trigger digitaltrigger
after update of stock on digital
for each row 
when (new.stock < 0)
execute procedure digital_update();



create or replace function sc_update() returns trigger as $$
	begin 
	update shoes_clothes
	set stock = new.stock + 1, sales_volume = new.sales_volume - 1
	where category_id = new.category_id;
	return new;
	end;
$$ language plpgsql;

create trigger sctrigger
after update of stock on shoes_clothes
for each row 
when (new.stock < 0)
execute procedure sc_update();


select * from shoes_clothes

update shoes_clothes 
set stock = 874
where category_id = 1
